
/**
 * 将字符串中的全角字符转换为半角字符。
 * @param {String} str 
 * @returns 
 */
 function DBC2SBC(str) {
    if(str == null || str == undefined) return "";
    var result = "";
    for (var i = 0; i < str.length; i++) {
        code = str.charCodeAt(i);    //获取当前字符的unicode编码
        if (code >= 65281 && code <= 65373)   //在这个unicode编码范围中的是所有的英文字母已经各种字符
        {
            var d = str.charCodeAt(i) - 65248;
            result += String.fromCharCode(d);   //把全角字符的unicode编码转换为对应半角字符的unicode码
        }
        else if (code == 12288)//空格
        {
            var d = str.charCodeAt(i) - 12288 + 32;
            result += String.fromCharCode(d);
        }
        else {
            result += str.charAt(i);
        }
    }
    return result;
}

/**
 * 逐次将记录填充到命名单元格区域中。
 */
let recordRealIndex = 2;  //行1 是标题行，不是记录行。

/**
 * 逐次填充记录数据到模板文件中，然后，复制模板文件的 usedrange 到新文件中的对应位置。
 */
function onbuttonclick(idStr) {
    switch (idStr) {
        case "btnRefresh": {
            // 初始化选择器
            const doc = wps.EtApplication().ActiveWorkbook
            if (!doc) {
                alert("当前没有打开任何文档")
                return;
            }

            let temp = document.getElementById("datatemp");  // 模板表选择器备选项列表
            let data = document.getElementById("datasrc");  // 数据源表选择器备选项列表

            temp.innerHTML = "";
            data.innerHTML = "";

            for (var i = 1; i <= doc.Sheets.Count; i++) {
                var sheet = doc.Sheets.Item(i);
                temp.options.add(new Option(sheet.Name, sheet.Name));   // Text, Value
                data.options.add(new Option(sheet.Name, sheet.Name));   // Text, Value
            }
            break;
        }
        case "btnOk": {
            let selSrc = document.getElementById("datasrc");
            let srcSheetName = selSrc.value;
            if (!srcSheetName) {
                alert("请设置数据源表！")
                return;
            }

            let selTmp = document.getElementById("datatemp")
            let tmpSheetName = selTmp.value;
            if (!tmpSheetName) {
                alert("请设置数据模板表！");
                return;
            }

            let selColumns = document.getElementById("columnsCount");
            let blockColumnCount = parseInt(selColumns.value);
            if (!blockColumnCount || blockColumnCount < 1) {
                blockColumnCount = 1;
            }

            let actWorkbook = wps.EtApplication().ActiveWorkbook;

            let srcSheet = actWorkbook.Sheets.Item(srcSheetName);
            if (!srcSheet) {
                alert("找不到数据源表！");
                return;
            }
            let tmpSheet = actWorkbook.Sheets.Item(tmpSheetName);
            if (!tmpSheet) {
                alert("找不到数据模板表！");
                return;
            }else{
                if(tmpSheet.UsedRange.Columns.Count > 100 || tmpSheet.UsedRange.Rows.Count > 200){
                    alert("检测到填充模板表使用的行数超过200或列数超过100。很难想像需要这么大的模板。请检查是否在需要的模板内容之外的其它单元格中误输入了内容或者设置了格式。请尽量删除所有多作的行/列。");
                    return;
                }
            }

            let fieldLineNumberInput = document.getElementById("fieldLineNumber");
            let fieldLineNumber = 1;
            if (fieldLineNumberInput) {
                fieldLineNumber = parseInt(fieldLineNumberInput.value);
            }

            // 初始化记录索引
            recordRealIndex = fieldLineNumber + 1;  //行1 是字段名行，不是记录行。

            // 将源数据表的信息记录下来备用
            let used = srcSheet.UsedRange;
            if (used.Rows.Count <= 1) {
                alert("源数据表中无数据！要求指定准确的字段名行，且字段名对应模板表中的单元格命名。");
                return;
            }

            // 记录标题行各字段名（模板文件中的命名单元格即用这些字段名称由用户手动命名）
            let fstLineRange = wps.EtApplication().Intersect(srcSheet.UsedRange, srcSheet.Rows.Item(fieldLineNumber));

            let names = wps.EtApplication().Names;
            if (!names || names.Count <= 0) {
                alert("未找到用以填充记录字段的命名区域，请确保设置的字段名列表行行号正确或填写了正确的字段名！");
                return;
            }

            let newSheet = actWorkbook.Sheets.Add();
            let recordRelativeIndex = 1;
            let rCount = 0;   // 已输出纵向块数
            let cCount = 0;    // 已输出横向块数，每碰到取余数为零后即归零，同时rCount 加1。

            var ckxAutoFormatBoolValue = document.getElementById("ckxAutoFormatBoolValue")
            var autoFormatBoolValue = ckxAutoFormatBoolValue.checked;
        
            for (recordRealIndex; recordRealIndex <= (srcSheet.UsedRange.Row + srcSheet.UsedRange.Rows.Count - 1); recordRealIndex++) {  // 索引从1开始，第一行是标题（包括字段名）
                // 逐次将记录填写到模板文件中
                let recordLineRange = wps.EtApplication().Intersect(srcSheet.Rows.Item(recordRealIndex), srcSheet.UsedRange);

                if (fstLineRange.Cells.Count != recordLineRange.Cells.Count) continue;

                for (let i = 1; i <= names.Count; i++) {
                    let name = names.Item(i);
                    if ((name._Default.startsWith("=\'" + tmpSheetName + "\'!") == false && 
                         name._Default.startsWith("=" + tmpSheetName + "!") == false) || 
                         name.RefersToRange == null) continue;
                    var valText = getFieldValueFromArray(recordLineRange, fstLineRange, name.Name);
                    if(autoFormatBoolValue && valText != null){
                        valText = trimStr(DBC2SBC(valText.toString()));
                        var lowerValText = valText.toLowerCase();
                        if(lowerValText == "是"){
                            name.RefersToRange.Value2 = "■是 □否";
                        }else if(lowerValText == "否"){
                            name.RefersToRange.Value2 = "□是 ■否";
                        }else if(lowerValText == "true"){
                            name.RefersToRange.Value2 = "■True □False";
                        }else if(lowerValText == "false"){
                            name.RefersToRange.Value2 = "□True ■False";
                        }else{
                            name.RefersToRange.Value2 = (valText == null ? "" : valText);
                        }
                    }else{
                        name.RefersToRange.Value2 = (valText == null ? "" : valText);
                    }
                }

                tmpSheet.Activate();
                if ((recordRelativeIndex - 1) % blockColumnCount == 0) {
                    var rowsIndexText = tmpSheet.UsedRange.Row.toString() + ":" + (tmpSheet.UsedRange.Row + tmpSheet.UsedRange.Rows.Count - 1).toString();
                    var rows = tmpSheet.Rows.Item(rowsIndexText);
                    rows.Select();
                    rows.Copy();
                }
                else {
                    tmpSheet.UsedRange.Select();
                    tmpSheet.UsedRange.Copy();
                }
                newSheet.Activate();

                let pasteRowIndex = (tmpSheet.UsedRange.Rows.Count + 1) * rCount + 1;
                let pasteColumnIndex = (tmpSheet.UsedRange.Columns.Count + 1) * cCount + 1;

                var ckxInsertPageBreaker = document.getElementById("ckxInsertPageBreaker")

                if ((recordRelativeIndex - 1) % blockColumnCount == 0) {
                    var rowIndexTxt = pasteRowIndex.toString() + ":" + (pasteRowIndex + tmpSheet.UsedRange.Rows.Count - 1).toString();
                    newSheet.Rows.Item(rowIndexTxt).Select();

                    // 插入分页符
                    if (ckxInsertPageBreaker.checked) {
                        newSheet.HPageBreaks.Add(newSheet.Cells.Item(pasteRowIndex, 1));
                    }
                }
                else {
                    newSheet.Cells.Item(pasteRowIndex, pasteColumnIndex).Select();
                }
                newSheet.PasteSpecial(13);
                //newSheet.PasteSpecial(13);  // 13,xlPasteAllUsingSourceTheme，使用源主题粘贴全部内容。

                if (recordRelativeIndex % blockColumnCount == 0) {
                    rCount += 1;
                    cCount = 0;  // 归零
                } else {
                    cCount += 1;  // 横向输出
                }

                recordRelativeIndex++;
            }
            break;
        }
        case "btnExportImages": {
            let selSrc = document.getElementById("datasrc");
            let srcSheetName = selSrc.value;
            if (!srcSheetName) {
                alert("请设置数据源表！")
                return;
            }

            let selTmp = document.getElementById("datatemp")
            let tmpSheetName = selTmp.value;
            if (!tmpSheetName) {
                alert("请设置数据模板表！");
                return;
            }

            let selColumns = document.getElementById("columnsCount");
            let blockColumnCount = parseInt(selColumns.value);
            if (!blockColumnCount || blockColumnCount < 1) {
                blockColumnCount = 1;
            }

            let actWorkbook = wps.EtApplication().ActiveWorkbook;

            let srcSheet = actWorkbook.Sheets.Item(srcSheetName);
            if (!srcSheet) {
                alert("找不到数据源表！");
                return;
            }
            let tmpSheet = actWorkbook.Sheets.Item(tmpSheetName);
            if (!tmpSheet) {
                alert("找不到数据模板表！");
                return;
            }

            let fieldLineNumberInput = document.getElementById("fieldLineNumber");
            let fieldLineNumber = 1;
            if (fieldLineNumberInput) {
                fieldLineNumber = parseInt(fieldLineNumberInput.value);
            }

            // 初始化记录索引
            recordRealIndex = fieldLineNumber + 1;  //行1 是字段名行，不是记录行。

            // 将源数据表的信息记录下来备用
            let used = srcSheet.UsedRange;
            if (used.Rows.Count <= 1) {
                alert("源数据表中无数据！要求指定准确的字段名行，且字段名对应模板表中的单元格命名。");
                return;
            }

            // 记录标题行各字段名（模板文件中的命名单元格即用这些字段名称由用户手动命名）
            let fstLineRange = wps.EtApplication().Intersect(srcSheet.UsedRange, srcSheet.Rows.Item(fieldLineNumber));

            let names = wps.EtApplication().Names;
            if (!names || names.Count <= 0) {
                alert("未找到用以填充记录字段的命名区域，请确保设置的字段名列表行行号正确或填写了正确的字段名！");
                return;
            }

            // TODO: 还不能支持文件命名规则。实在太累了，2021年10月15日13时46分。
            let fileNameRuleInput = document.getElementById("inputFileNameRule");
            let fileNameRuleValue = fileNameRuleInput.value;

            let p = /(?<=\{)[^\{\}\-]{1,}(?=\})/g;
            let fileNamePieces = fileNameRuleValue.match(p);
            // 将形如：{序号}-{年级}-{班级}-{姓名}-{身份证号} 的规则中花括号对中的片段提取出来。

            let pieces = new Array();
            if (fileNamePieces.length <= 0) {
                pieces.push({ name: "序号", value: "" });
            } else {
                fileNamePieces.forEach(element => {
                    pieces.push({ name: element, value: "" });
                });
            }

            var ckxAutoFormatBoolValue = document.getElementById("ckxAutoFormatBoolValue")
            var autoFormatBoolValue = ckxAutoFormatBoolValue.checked;
        
            for (recordRealIndex; recordRealIndex < (srcSheet.UsedRange.Row + srcSheet.UsedRange.Rows.Count); recordRealIndex++) {  // 索引从1开始，第一行是标题（包括字段名）
                // 逐次将记录填写到模板文件中
                let recordLineRange = wps.EtApplication().Intersect(srcSheet.Rows.Item(recordRealIndex), srcSheet.UsedRange);

                if (fstLineRange.Cells.Count != recordLineRange.Cells.Count) continue;

                for (let i = 1; i <= names.Count; i++) {
                    let name = names.Item(i);
                    if ((name._Default.startsWith("=\'" + tmpSheetName + "\'!") == false && 
                         name._Default.startsWith("=" + tmpSheetName + "!") == false) || 
                         name.RefersToRange == null) continue;
                    var valText = getFieldValueFromArray(recordLineRange, fstLineRange, name.Name);
                    if(autoFormatBoolValue && valText != null){
                        valText = trimStr(DBC2SBC(valText.toString()));
                        var lowerValText = valText.toLowerCase();
                        if(lowerValText == "是"){
                            name.RefersToRange.Value2 = "■是 □否";
                        }else if(lowerValText == "否"){
                            name.RefersToRange.Value2 = "□是 ■否";
                        }else if(lowerValText == "true"){
                            name.RefersToRange.Value2 = "■True □False";
                        }else if(lowerValText == "false"){
                            name.RefersToRange.Value2 = "□True ■False";
                        }else{
                            name.RefersToRange.Value2 = (valText == null ? "" : valText);
                        }
                    }else{
                        name.RefersToRange.Value2 = (valText == null ? "" : valText);
                    }
                }

                tmpSheet.UsedRange.CopyPicture(Apperance = 1, Format = 2);  // 复制图像文件
                var chartObject = tmpSheet.ChartObjects().Add(tmpSheet.UsedRange.Left, tmpSheet.UsedRange.Top, tmpSheet.UsedRange.Width, tmpSheet.UsedRange.Height);
                chartObject.Name = "ChartVolumeMetricsDevEXPORT" + recordRealIndex;
                var chart = tmpSheet.ChartObjects("ChartVolumeMetricsDevEXPORT" + recordRealIndex).Chart;
                chart.Paste();

                // 读取、拼接文件短名。
                var shortName = "";
                for (let i = 0; i < pieces.length; i++) {
                    var piece = pieces[i];
                    piece.value = getFieldValueFromArray(recordLineRange, fstLineRange, piece.name);
                    shortName += piece.value;
                    shortName += '-';
                }

                shortName = shortName.substring(0, shortName.length - 1);

                let doc = wps.EtApplication().ActiveWorkbook
                let path = doc.Path + "\\" + doc.Name;
                if (path.length <= 0) {
                    path = "D:\\LunarEt-导出图像\\";
                } else {
                    path += "-导出图像";
                    if (path[path.length - 1] != "\\") {
                        path += "\\";
                    }
                }

                if (wps.EtApplication().FileSystem.Exists(path) == false) {
                    wps.EtApplication().FileSystem.Mkdir(path);
                }

                if (shortName.length > 0) {
                    chart.Export(path + shortName + ".jpg");
                }
                else {
                    chart.Export(path + (recordRealIndex - 1) + ".jpg");
                }

                chart.Delete();
                tmpSheet.ChartObjects().Delete();
            }
            break;
        }
    }
}

let previewIndex = 1;

function PreviewByIndex(recordIndex) {
    let selSrc = document.getElementById("datasrc");
    let srcSheetName = selSrc.value;
    if (!srcSheetName) {
        alert("请设置数据源表！")
        return;
    }

    let selTmp = document.getElementById("datatemp")
    let tmpSheetName = selTmp.value;
    if (!tmpSheetName) {
        alert("请设置数据模板表！");
        return;
    }

    let selColumns = document.getElementById("columnsCount");
    let blockColumnCount = parseInt(selColumns.value);
    if (!blockColumnCount || blockColumnCount < 1) {
        blockColumnCount = 1;
    }

    let actWorkbook = wps.EtApplication().ActiveWorkbook;

    let srcSheet = actWorkbook.Sheets.Item(srcSheetName);
    if (!srcSheet) {
        alert("找不到数据源表！");
        return;
    }
    let tmpSheet = actWorkbook.Sheets.Item(tmpSheetName);
    if (!tmpSheet) {
        alert("找不到数据模板表！");
        return;
    }

    var preIndexInput = document.getElementById('iptIndex');
    preIndexInput.value = previewIndex;
    // var msgPara = document.getElementById('msg');   // 效果不明显，不必要。
    // msgPara.innerHTML = "";

    // 将源数据表的信息记录下来备用
    let used = srcSheet.UsedRange;
    if (used.Rows.Count <= 1) {
        alert("源数据表中无数据！要求指定准确的字段名行，且字段名对应模板表中的单元格命名。");
        return;
    }

    // 记录标题行各字段名（模板文件中的命名单元格即用这些字段名称由用户手动命名）
    let fstLineRange = wps.EtApplication().Intersect(srcSheet.UsedRange, srcSheet.Rows.Item(used.Row));

    let names = wps.EtApplication().Names;
    if (!names || names.Count <= 0) {
        alert("未找到用以填充记录字段的命名区域，请确保设置的字段名列表行行号正确或填写了正确的字段名！");
        return;
    }

    lastPreviewIndex = used.Rows.Count - 1;
    recordRealIndex = recordIndex + used.Row;
    // 逐次将记录填写到模板文件中
    let recordLineRange = wps.EtApplication().Intersect(srcSheet.Rows.Item(recordRealIndex), srcSheet.UsedRange);

    var ckxAutoFormatBoolValue = document.getElementById("ckxAutoFormatBoolValue")
    var autoFormatBoolValue = ckxAutoFormatBoolValue.checked;

    if (fstLineRange.Cells.Count != recordLineRange.Cells.Count) return;
    wps.EtApplication.ScreenUpdating = false;  // 禁止屏幕刷新
    for (let i = 1; i <= names.Count; i++) {
        let name = names.Item(i);
        if ((name._Default.startsWith("=\'" + tmpSheetName + "\'!") == false &&
             name._Default.startsWith("=" + tmpSheetName + "!") == false) || 
             name.RefersToRange == null) continue;
        var foundValue = getFieldValueFromArray(recordLineRange, fstLineRange, name.Name);

        var valText = (foundValue == null ? "" : foundValue);
        valText = trimStr(DBC2SBC(valText.toString()));
        if(autoFormatBoolValue && valText != null){
            var lowerValText = valText.toLowerCase();
            if(lowerValText == "是"){
                name.RefersToRange.Value2 = "■是 □否";
            }else if(lowerValText == "否"){
                name.RefersToRange.Value2 = "□是 ■否";
            }else if(lowerValText == "true"){
                name.RefersToRange.Value2 = "■True □False";
            }else if(lowerValText == "false"){
                name.RefersToRange.Value2 = "□True ■False";
            }else{
                name.RefersToRange.Value2 = valText;
            }
        }else{
            name.RefersToRange.Value2 = valText;
        }
    }

    wps.EtApplication.ScreenUpdating = true;  // 恢复屏幕刷新

    //msgPara.innerHTML = "已填充当前记录到模板表！";   // 效果不明显，不必要
}

function OnPreview(idStr) {
    switch (idStr) {
        case "First": {
            previewIndex = 1;
            PreviewByIndex(previewIndex);
            break;
        }
        case "Preview": {
            if (previewIndex > 1) {
                PreviewByIndex(--previewIndex);
            }
            break;
        }
        case "Next": {
            let selSrc = document.getElementById("datasrc");
            let srcSheetName = selSrc.value;
            if (!srcSheetName) {
                alert("请设置数据源表！")
                return;
            }
            let actWorkbook = wps.EtApplication().ActiveWorkbook;
            let srcSheet = actWorkbook.Sheets.Item(srcSheetName);
            if (!srcSheet) {
                alert("找不到数据源表！");
                return;
            }
            if (previewIndex < srcSheet.UsedRange.Rows.Count - 1) {
                PreviewByIndex(++previewIndex);
            }
            break;
        }
        case "Last": {
            let selSrc = document.getElementById("datasrc");
            let srcSheetName = selSrc.value;
            if (!srcSheetName) {
                alert("请设置数据源表！")
                return;
            }
            let actWorkbook = wps.EtApplication().ActiveWorkbook;
            let srcSheet = actWorkbook.Sheets.Item(srcSheetName);
            if (!srcSheet) {
                alert("找不到数据源表！");
                return;
            }
            previewIndex = srcSheet.UsedRange.Rows.Count - 1;
            PreviewByIndex(previewIndex);
            break;
        }
        case "JumpTo":{
            var preIndexInput = document.getElementById('iptIndex');
            previewIndex = parseInt(preIndexInput.value);
            let selSrc = document.getElementById("datasrc");
            let srcSheetName = selSrc.value;
            if (!srcSheetName) {
                alert("请设置数据源表！")
                return;
            }
            let actWorkbook = wps.EtApplication().ActiveWorkbook;
            let srcSheet = actWorkbook.Sheets.Item(srcSheetName);
            if (!srcSheet) {
                alert("找不到数据源表！");
                return;
            }
            PreviewByIndex(previewIndex);
            break;
        }
    }
}

/**
 *  这个是旧版，效率很差，新版改用数组，速度快很多。请使用 getFieldValueFromArray() 方法。
 */
let getFieldValue = function (recordLineRange, fstLineRange, name) {
    let index = 0;
    for (let i = 1; i <= fstLineRange.Cells.Count; i++) {
        let fieldName = fstLineRange.Cells.Item(i);
        if (fieldName.Value2 == name) {
            index = i;
            break;
        }
    }

    if (index < 1) return "";
    var resultCell = recordLineRange.Cells.Item(index);
    return resultCell.Value2;  // 这个索引不是从0开始的。
}

let getFieldValueFromArray = function(recordLineRange, fstLineRange, name) {
    let recordLineArray = recordLineRange.Value2[0];
    let fstLineArray = fstLineRange.Value2[0];

    let array= name.split("!");
    if(array.length == 2){
        name = array[1];
    }

    let index = 0;
    for (let i = 0; i < fstLineArray.length; i++) {    // 改用数组实现，索引是从0开始的
        let fieldName = fstLineArray[i];
        if (trimStr(DBC2SBC(fieldName)).toLowerCase() == name.toLowerCase()) {
            index = i;
            break;
        }
    }

    if (index < 0) return "";
    var resultValue = recordLineArray[index];
    return resultValue;  // 改用数组实现，索引是从0开始的
}

/**
 * 去除首尾空格符
 * @param {String} str 
 * @returns 
 */
 function trimStr(str) {
    if(str == null || str == undefined) return "";
    return str.replace(/(^\s*)|(\s*$)/g, "");
}

window.onload = () => {
    // var xmlReq = WpsInvoke.CreateXHR();
    // var url = location.origin + "/.debugTemp/NotifyDemoUrl"
    // xmlReq.open("GET", url);
    // xmlReq.onload = function (res) {
    //     //var node = document.getElementById("DemoSpan");
    //     //window.document.getElementById("DemoSpan").innerHTML = res.target.responseText;
    // };
    // xmlReq.send();

}